package com.situ.scientific.dao.impl;

import com.situ.scientific.dao.IProjectDao;
import com.situ.scientific.pojo.Project;
import com.situ.scientific.pojo.query.ProjectQuery;
import com.situ.scientific.pojo.vo.ProjectIdVo;
import com.situ.scientific.pojo.vo.ProjectVo;
import com.situ.scientific.pojo.vo.ResearchersVo;
import com.situ.scientific.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;

public class ProjectDaoImpl implements IProjectDao {
    JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    @Override
    public List<ProjectVo> selectByPage(ProjectQuery projectQuery) {
        List<Object> queryList = new ArrayList<>();
        /*"SELECT p.id,p.`name`,r.`name` AS researchers,r.faculty,p.declare_funds AS declareFunds,p.content,p.deleted,e.approve_id AS approvalProgress,p.`commit`" +
                " FROM project p " +
                " JOIN researchers r ON p.researchers_id=r.id" +
                " LEFT JOIN examine e ON p.id = e.project_no" +
                " WHERE 1=1"*/
        StringBuilder sql = new StringBuilder(
                "SELECT p.id,p.`name`,r.`name` AS researchers,r.faculty,p.declare_funds AS declareFunds,p.content,p.deleted,p.approve_id AS approveId,p.`commit`" +
                        " FROM project p " +
                        " JOIN researchers r ON p.researchers_id=r.id" +
                        " WHERE 1=1"
        );
        String queryName = projectQuery.getName();
        if (queryName != null && !"".equals(queryName)) {
            sql.append(" AND p.name LIKE ?");
            queryList.add("%" + queryName + "%");
        }

        String facultyList = projectQuery.getFaculty();
        if (facultyList != null && !"".equals(facultyList)) {
            sql.append(" AND r.faculty = ?");
            queryList.add(facultyList);
        }
        sql.append(" LIMIT ?,?");

        int offset = (projectQuery.getPage()-1)*projectQuery.getLimit();
        queryList.add(offset);
        queryList.add(projectQuery.getLimit());
        return template.query(sql.toString(),new BeanPropertyRowMapper<>(ProjectVo.class),queryList.toArray());
    }

    @Override
    public int totalCount(ProjectQuery projectQuery) {
        List<Object> queryList = new ArrayList<>();
        StringBuilder sql = new StringBuilder(
                "SELECT COUNT(*) " +
                        " FROM project p " +
                        " JOIN researchers r ON p.researchers_id=r.id" +
                        " WHERE 1=1"
        );
        String queryName = projectQuery.getName();
        if (queryName != null && !"".equals(queryName)) {
            sql.append(" AND p.name LIKE ?");
            queryList.add("%" + queryName + "%");
        }

        String facultyList = projectQuery.getFaculty();
        if (facultyList != null && !"".equals(facultyList)) {
            sql.append(" AND r.faculty = ?");
            queryList.add(facultyList);
        }
        return template.queryForObject(sql.toString(), Integer.class, queryList.toArray());
    }

    @Override
    public void updateStatus(String id, int i) {
        String sql = "UPDATE project SET deleted=? WHERE id=?";
        template.update(sql,i,id);
    }

    @Override
    public void updateApprove(String id, int status) {
        String sql = "UPDATE project SET approve_id=? WHERE id=?";
        template.update(sql,status,id);
    }

    @Override
    public ProjectIdVo selectById(String id) {
        String sql = "SELECT \n" +
                "    p.id,\n" +
                "    p.name,\n" +
                "    r.faculty,\n" +
                "    e.approval_progress,\n" +
                "\t\tp.`commit`\n" +
                "FROM \n" +
                "    project p\n" +
                "LEFT JOIN \n" +
                "    researchers r ON p.researchers_id = r.id\n" +
                "LEFT JOIN \n" +
                "    examine e ON p.id = e.project_no\n" +
                "WHERE \n" +
                "    p.id = ?";
        return template.queryForObject(sql,new BeanPropertyRowMapper<>(ProjectIdVo.class),id);
    }

    @Override
    public void add(Project project) {
        String sql = "INSERT INTO project(id,name,researchers_id,content) VALUES (?,?,?,?)";
        template.update(sql,project.getId(),project.getName(),project.getResearchersId(),project.getContent());
    }

    @Override
    public void update(Project project) {
        String sql = "UPDATE project SET commit = ? WHERE id = ?";
        template.update(sql, project.getCommit(),project.getId());
    }


}
